Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# CREATE PROCEDURE

Purpose

Use the `CREATE` `PROCEDURE` statement to create a standalone stored procedure or a call specification.

A procedure is a group of PL/SQL statements that you can call by name. A call specification (sometimes called call spec) declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call spec tells Oracle Database which Java method to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value.

Stored procedures offer advantages in the areas of development, integrity, security, performance, and memory allocation.

See Also: Oracle Database Application Developer's Guide - Fundamentals for more information on stored procedures, including how to call stored procedures and for information about registering external procedures CREATE FUNCTION for information specific to functions, which are similar to procedures in many ways CREATE PACKAGE for information on creating packages. The `CREATE` `PROCEDURE` statement creates a procedure as a standalone schema object. You can also create a procedure as part of a package. ALTER PROCEDURE and DROP PROCEDURE for information on modifying and dropping a standalone procedure CREATE LIBRARY for more information about shared libraries

Prerequisites ``

Before creating a procedure, the user `SYS` must run a SQL script commonly called `DBMSSTDX.SQL`. The exact name and location of this script depend on your operating system.

To create a procedure in your own schema, you must have the `CREATE` `PROCEDURE` system privilege. To create a procedure in another user's schema, you must have the `CREATE` `ANY` `PROCEDURE` system privilege. To replace a procedure in another schema, you must have the `ALTER` `ANY` `PROCEDURE` system privilege.

To invoke a call spec, you may need additional privileges, for example, the `EXECUTE` object privilege on the C library for a C call spec.

To embed a `CREATE` `PROCEDURE` statement inside an Oracle precompiler program, you must terminate the statement with the keyword `END-EXEC` followed by the embedded SQL statement terminator for the specific language.

See Also: PL/SQL User's Guide and Reference or Oracle Database Java Developer's Guide for more information

Syntax

create_procedure::=

Description of the illustration create_procedure.gif

invoker_rights_clause::=

Description of the illustration invoker_rights_clause.gif

call_spec::=

Description of the illustration call_spec.gif

Java_declaration::=

Description of the illustration Java_declaration.gif

C_declaration::=

Description of the illustration C_declaration.gif

Semantics

OR REPLACE

Specify `OR` `REPLACE` to re-create the procedure if it already exists. Use this clause to change the definition of an existing procedure without dropping, re-creating, and regranting object privileges previously granted on it. If you redefine a procedure, then Oracle Database recompiles it.

Users who had previously been granted privileges on a redefined procedure can still access the procedure without being regranted the privileges.

If any function-based indexes depend on the package, then Oracle Database marks the indexes `DISABLED`.

See Also: ALTER PROCEDURE for information on recompiling procedures

schema

Specify the schema to contain the procedure. If you omit `schema`, then the database creates the procedure in your current schema.

procedure

Specify the name of the procedure to be created.

If creating the procedure results in compilation errors, then the database returns an error. You can see the associated compiler error messages with the SQL*Plus command `SHOW` `ERRORS`.

argument

Specify the name of an argument to the procedure. If the procedure does not accept arguments, you can omit the parentheses following the procedure name.

IN Specify `IN` to indicate that you must supply a value for the argument when calling the procedure.

OUT Specify `OUT` to indicate that the procedure passes a value for this argument back to its calling environment after execution.

IN OUT Specify `IN` `OUT` to indicate that you must supply a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution.

If you omit `IN`, `OUT,` and `IN` `OUT`, then the argument defaults to `IN`.

NOCOPY Specify `NOCOPY` to instruct the database to pass this argument as fast as possible. This clause can significantly enhance performance when passing a large value like a record, an index-by table, or a varray to an `OUT` or `IN` `OUT` parameter. `IN` parameter values are always passed `NOCOPY`.

- When you specify `NOCOPY`, assignments made to a package variable may show immediately in this parameter, or assignments made to this parameter may show immediately in a package variable, if the package variable is passed as the actual assignment corresponding to this parameter.

- Similarly, changes made either to this parameter or to another parameter may be visible immediately through both names if the same variable is passed to both.

- If the procedure is exited with an unhandled exception, then any assignment made to this parameter may be visible in the caller's variable.

These effects may or may not occur on any particular call. You should use `NOCOPY` only when these effects would not matter.

datatype Specify the datatype of the argument. An argument can have any datatype supported by PL/SQL.

Datatypes cannot specify length, precision, or scale. For example, `VARCHAR2(10)` is not valid, but `VARCHAR2` is valid. Oracle Database derives the length, precision, and scale of an argument from the environment from which the procedure is called.

DEFAULT expr Use this clause to specify a default value for the argument. Oracle Database recognizes the characters `:=` in place of the keyword `DEFAULT`.

invoker_rights_clause

The `invoker_rights_clause` lets you specify whether the procedure executes with the privileges and in the schema of the user who owns it or with the privileges and in the schema of `CURRENT_USER`.

This clause also determines how the database resolves external names in queries, DML operations, and dynamic SQL statements in the procedure.

AUTHID CURRENT_USER

Specify `CURRENT_USER` to indicate that the procedure executes with the privileges of `CURRENT_USER`. This clause creates an invoker-rights procedure.

This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of `CURRENT_USER`. External names in all other statements resolve in the schema in which the procedure resides.

AUTHID DEFINER

Specify `DEFINER` to indicate that the procedure executes with the privileges of the owner of the schema in which the procedure resides, and that external names resolve in the schema where the procedure resides. This is the default and creates a definer-rights procedure.

See Also: PL/SQL User's Guide and Reference Oracle Database Concepts and Oracle Database Application Developer's Guide - Fundamentals for information on how `CURRENT_USER` is determined

IS | AS Clause

Use the appropriate part of this clause to declare the procedure.

pl/sql_subprogram_body

Declare the procedure in a PL/SQL subprogram body.

See Also: Oracle Database Application Developer's Guide - Fundamentals for more information on PL/SQL subprograms

call_spec

Use the `call_spec` to map a Java or C method name, parameter types, and return type to their SQL counterparts.

In the `Java_declaration`, `string` identifies the Java implementation of the method.

See Also: Oracle Database Java Developer's Guide for an explanation of the parameters and semantics of the `Java_declaration` Oracle Database Application Developer's Guide - Fundamentals for an explanation of the parameters and semantics of the `C_declaration`

AS EXTERNAL In earlier releases, the `AS` `EXTERNAL` clause was an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle recommends that you use the `AS` `LANGUAGE` `C` syntax.

Examples

Creating a Procedure: Example The following statement creates the procedure `remove_emp` in the schema `hr`. The PL/SQL is shown in italics:

    CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
       tot_emps NUMBER;
       BEGIN
          DELETE FROM employees
          WHERE employees.employee_id = remove_emp.employee_id;
       tot_emps := tot_emps - 1;
       END;
    /

The `remove_emp` procedure removes a specified employee. When you call the procedure, you must specify the `employee_id` of the employee to be removed.

The procedure uses a `DELETE` statement to remove from the `employee`s table the row of `employee_id`.

See Also: "Creating a Package Body: Example" to see how to incorporate this procedure into a package

In the following example, external procedure `c_find_root` expects a pointer as a parameter. Procedure `find_root` passes the parameter by reference using the `BY` `REFERENCE` phrase. The PL/SQL is shown in italics:

    CREATE PROCEDURE find_root
       ( x IN REAL )
       IS LANGUAGE C
          NAME c_find_root
          LIBRARY c_utils
          PARAMETERS ( x BY REFERENCE );